--EXEC sp_create_update_Prog_inventory_lot 40463,300,3,'Carsem','Admin', 2345
CREATE PROCEDURE SP_CREATE_UPDATE_PROG_INVENTORY_LOT
@LOT_ID				INT,
@SHIP_DIE_QTY		INT,
@SCRAP_QTY			INT,
@NEXT_LOCATION     VARCHAR(50),
@USER_NAME			VARCHAR(50),
@TO_PROCESSSTEP_ID	INT

AS 
BEGIN
	
	
	DECLARE @DATE_OUT	DATETIME
	DECLARE @NEW_LOT_ID INT
	DECLARE @NEW_LOT_LOC_ID INT
	DECLARE @LOT_NUMBER VARCHAR(50)
	DECLARE @FAB_LOTNO	VARCHAR(50)
	DECLARE @LOT_CATEGORY	 VARCHAR(50)
	DECLARE @QTY_IN	INT
	DECLARE @LOT_CURRENT_STATUS VARCHAR(50)

	SET @DATE_OUT = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)

	SELECT @NEW_LOT_LOC_ID = LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = @NEXT_LOCATION AND L_TYPE = 'Finished Goods'
	
	Select @LOT_NUMBER = LOT_NUMBER, @FAB_LOTNO =FAB_LOT_NUMBER, @LOT_CATEGORY = LOT_CATEGORY FROM LOT_Table where Lot_id = @LOT_ID
	
	--INSERTING SHIPPiNG RECORED
	INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, STATUS, SHIP_STATUS, SHIP_QTY, TIMESTAMP,  SL_NEXTLOCATION , SL_NEXT_PROCESS, Close_Date ) VALUES 
				( @LOT_ID, @DATE_OUT, 'Close', 1, @SHIP_DIE_QTY, CURRENT_TIMESTAMP, @NEXT_LOCATION , 'Finished Goods', @DATE_OUT )

	UPDATE LOT_TABLE SET QTY_IN = QTY_IN-(@SHIP_DIE_QTY+@SCRAP_QTY), USER_NAME = @USER_NAME WHERE LOT_ID = @LOT_ID

	IF @SCRAP_QTY > 0
	BEGIN				
			INSERT INTO SCRAPPED_LOTS ( LOT_ID, SL_SCRAPPED_DIE_QTY, SL_SCRAPPED_DATE, SL_GENERATED_BY, SL_GENERATED_ON, [TIMESTAMP] )
			VALUES ( @LOT_ID, @SCRAP_QTY, CONVERT( VARCHAR, GETDATE(), 101 ), @USER_NAME, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP )
	END
	
	SELECT @QTY_IN = ISNULL(QTY_IN, 0) FROM LOT_TABLE WHERE LOT_ID = @LOT_ID

	IF @QTY_IN = 0
	BEGIN
		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @LOT_ID
	END
	SELECT @NEW_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @TO_PROCESSSTEP_ID AND LOCATION_ID = @NEW_LOT_LOC_ID AND LOT_NUMBER = @LOT_NUMBER
	IF @NEW_LOT_ID IS NULL
	BEGIN

		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Qty_IN,  Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, LOT_DROP_OUT, USER_NAME) 
				values (@TO_PROCESSSTEP_ID, @NEW_LOT_LOC_ID, @LOT_NUMBER, @FAB_LOTNO, @LOT_NUMBER,  @SHIP_DIE_QTY,  @DATE_OUT, 'Running', CURRENT_TIMESTAMP, 0, @LOT_CATEGORY, 0 , @USER_NAME )
		select @NEW_LOT_ID = @@IDENTITY
		insert into Inventory_Lot( Lot_ID, Timestamp ) values (@NEW_LOT_ID, CURRENT_TIMESTAMP)
	END
	ELSE
	BEGIN
		SELECT @LOT_CURRENT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @NEW_LOT_ID
		IF @LOT_CURRENT_STATUS <> 'Close'
		BEGIN
			UPDATE LOT_TABLE SET QTY_IN = QTY_IN + @SHIP_DIE_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @NEW_LOT_ID
		END
		ELSE
		BEGIN
			UPDATE LOT_TABLE SET Lot_Current_Status = 'Running', QTY_IN = @SHIP_DIE_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @NEW_LOT_ID
		END
	END
END